﻿//   AppStorageServiceDatabase.cs
//   Copyright (c) 2012 Eswar Prakash
// 
//    This library is free software; you can redistribute it and/or modify  
//    it  under the terms of the GNU Lesser General Public License version  
//    2.1 as published by the Free Software Foundation.                     
//                                                                          
//    This library is distributed in the hope that it will be useful, but   
//    WITHOUT ANY WARRANTY; without even the implied warranty of            
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU     
//    Lesser General Public License for more details.                       
//                                                                          
//    You should have received a copy of the GNU Lesser General Public      
//    License along with this library; if not, write to the Free Software   
//    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  
//    USA
namespace CKSAS.AppStorage.SharedService
{
    using Core.Diagnostics;
    using System;
    using System.Collections.Generic;
    using System.Text;
    using Microsoft.SharePoint.Utilities;
    using Microsoft.SharePoint.Administration;
    using Microsoft.SharePoint.Administration.Backup;
    using System.Data.SqlClient;
    using System.Data;
    using System.Security.Principal;
    using Data;
    using System.Web.Hosting;

    /// <summary>
    /// Represents the service database instance for Application Storage Service
    /// </summary>
    internal sealed class AppStorageServiceDatabase : SPDatabase
    {
        /// <summary>
        /// Private constant to hold the path to the provisioning script for the database
        /// </summary>
        private const string ProvisioningSql = @"TEMPLATE\SQL\CKSAS\AppStorage\ProvisionDatabase.sql";

        /// <summary>
        /// Initializes a new instance of the <see cref="AppStorageServiceDatabase"/> class.
        /// </summary>
        public AppStorageServiceDatabase()
        {
        }

        /// <summary>
        /// Initializes a new instance of the <see cref="AppStorageServiceDatabase"/> class.
        /// </summary>
        /// <param name="parameters">The parameters.</param>
        internal AppStorageServiceDatabase(SPDatabaseParameters parameters)
            : base(parameters)
        {
        }

        /// <summary>
        /// Adds the database and its children to the backup/restore job. This method implements the <see cref="M:Microsoft.SharePoint.Administration.Backup.IBackupRestore.AddBackupObjects(Microsoft.SharePoint.Administration.Backup.SPBackupRestoreObject)"/> method that is defined in the <see cref="T:Microsoft.SharePoint.Administration.Backup.IBackupRestore"/> interface. The <see cref="T:Microsoft.SharePoint.Administration.SPDatabase"/> class can be backed up and restored and must therefore implement the <see cref="T:Microsoft.SharePoint.Administration.Backup.IBackupRestore"/> interface, but this method is only intended to be callable from catastrophic backup and restore infrastructure code in Windows SharePoint Services.
        /// </summary>
        /// <param name="parent">An <see cref="T:Microsoft.SharePoint.Administration.Backup.SPBackupRestoreObject"/> object from the parent of the database object.</param>
        public override void AddBackupObjects(SPBackupRestoreObject parent)
        {
            if (parent == null)
            {
                throw new ArgumentNullException("parent");
            }

            // backup / restore object
            var backupRestoreObject = parent.AddChild(this);
            backupRestoreObject.Information.SetParameter("SPTypeName", "Application Storage Service Database");
            backupRestoreObject.Information.SetParameter("SPDescription", String.Empty);
        }

        /// <summary>
        /// Makes the necessary changes to the local server to use the object.
        /// </summary>
        public override void Provision()
        {
            LoggingUtilities.WriteTraceEntry(DiagnosticsAreas.CKSAS, DiagnosticCategories.ServiceDatabase, TraceSeverity.Medium,
                                             "Starting provisioning of database: {0}", this.DatabaseConnectionString);
            base.Status = SPObjectStatus.Provisioning;
            this.Update();

            if (!Exists || (Exists && IsEmpty()))
            {
                var options = new Dictionary<string, bool>
                                  {
                                      {SqlDatabaseOption[2], false}
                                  };
                Provision(DatabaseConnectionString, SPUtility.GetGenericSetupPath(ProvisioningSql),
                                     options);
            }

            LoggingUtilities.WriteTraceEntry(DiagnosticsAreas.CKSAS, DiagnosticCategories.ServiceDatabase, TraceSeverity.Medium,
                                             "Successfully provisioned database: {0}", this.DatabaseConnectionString);
            base.Status = SPObjectStatus.Online;
            this.Update();
        }

        /// <summary>
        /// Grants the specified security identifier a process account 
        /// access to the databasse
        /// </summary>
        /// <param name="sid">The sid.</param>
        internal void GrantProcessAccountAccess(SecurityIdentifier sid)
        {
            base.GrantAccess(sid, "WSS_WPG");
        }

        /// <summary>
        /// Creates the specified parameters.
        /// </summary>
        /// <param name="parameters">The parameters.</param>
        /// <returns></returns>
        public static AppStorageServiceDatabase Create(SPDatabaseParameters parameters)
        {
            parameters.EnsureServiceInstance();

            LoggingUtilities.WriteTraceEntry(DiagnosticsAreas.CKSAS, DiagnosticCategories.ServiceDatabase, TraceSeverity.Medium,
                                             "Creating database instance: {0}", parameters.Database);
            var database = new AppStorageServiceDatabase(parameters);
            try
            {
                database.Update();
            }
            catch
            {
                LoggingUtilities.WriteTraceEntry(DiagnosticsAreas.CKSAS, DiagnosticCategories.ServiceDatabase,
                                                 TraceSeverity.Unexpected,
                                                 "There was an error creating database instance: {0}",
                                                 parameters.Database);
                throw;
            }

            return database;
        }

        /// <summary>
        /// Makes the necessary changes to the local server to clean up after deleting the database.
        /// </summary>
        public override void Unprovision()
        {
            LoggingUtilities.WriteTraceEntry(DiagnosticsAreas.CKSAS, DiagnosticCategories.ServiceDatabase, TraceSeverity.High,
                                             "Unprovisioning database with name {0} and connection string {1}",
                                             this.Name, this.DatabaseConnectionString);
            base.Unprovision();
        }

        /// <summary>
        /// Removes the object from the collection and the server farm.
        /// </summary>
        public override void Delete()
        {
            LoggingUtilities.WriteTraceEntry(DiagnosticsAreas.CKSAS, DiagnosticCategories.ServiceDatabase, TraceSeverity.High,
                                             "Dropping database: {0}", this.DatabaseConnectionString);
            base.Delete();
        }

        /// <summary>
        /// Maps to sp_AddStorageItem stored procedure
        /// </summary>
        /// <param name="WebApplicationId">Maps to @WebApplicationId parameter.</param>
        /// <param name="SiteId">Maps to @SiteId parameter.</param>
        /// <param name="WebId">Maps to @WebId parameter.</param>
        /// <param name="ListId">Maps to @ListId parameter.</param>
        /// <param name="Name">Maps to @Name parameter.</param>
        /// <param name="TypeFullName">Maps to @TypeFullName parameter.</param>
        /// <param name="CreatedBy">Maps to @CreatedBy parameter.</param>
        /// <param name="Data">Maps to @Data parameter.</param>
        /// <returns>
        /// An instance of <see cref="StoredProcedureResult"/> object
        /// </returns>
        internal StoredProcedureResult AddStorageItem(Guid WebApplicationId, Guid? SiteId, Guid? WebId, Guid? ListId,
            string Name, string TypeFullName, string CreatedBy, byte[] Data)
        {
            SqlConnection connection = null;

            try
            {
                using (HostingEnvironment.Impersonate())
                {
                    connection = (SqlConnection)this.Connection;

                    var command = new SqlCommand(StoredProcedureNames.AddStorageItem, connection);
                    command.CommandType = CommandType.StoredProcedure;

                    // add the parameters
                    command.Parameters.Add(this.CreateParameter("Name", SqlDbType.NVarChar, 50, Name));
                    command.Parameters.Add(this.CreateParameter("WebApplicationId", SqlDbType.UniqueIdentifier, null,
                                                                WebApplicationId));
                    command.Parameters.Add(this.CreateNullableParameter("SiteId", SqlDbType.UniqueIdentifier, null,
                                                                SiteId));
                    command.Parameters.Add(this.CreateNullableParameter("WebId", SqlDbType.UniqueIdentifier, null,
                                                                WebId));
                    command.Parameters.Add(this.CreateNullableParameter("ListId", SqlDbType.UniqueIdentifier, null,
                                                                ListId));
                    command.Parameters.Add(this.CreateParameter("TypeFullName", SqlDbType.NVarChar, 256, TypeFullName));
                    command.Parameters.Add(this.CreateParameter("CreatedBy", SqlDbType.NVarChar, 256, CreatedBy));
                    command.Parameters.Add(this.CreateParameter("Data", SqlDbType.VarBinary, -1, Data));

                    // write to trace and execute
                    this.WriteSqlCommandTrace(command);

                    var reader = command.ExecuteReader();

                    return new StoredProcedureResult()
                    {
                        DataReader = reader,
                        ExecutionType = StoredProcedureExecutionType.ExecuteReader
                    };
                }
            }
            catch (Exception exception)
            {
                // ensure connection is closed
                if (connection != null)
                {
                    if (connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }
                }

                this.WriteErrorLog(exception, EventSeverity.Error);
                throw;
            }
        }

        /// <summary>
        /// Maps to sp_AddStorageItem stored procedure
        /// </summary>
        /// <param name="WebApplicationId">Maps to @WebApplicationId parameter.</param>
        /// <param name="SiteId">Maps to @SiteId parameter.</param>
        /// <param name="WebId">Maps to @WebId parameter.</param>
        /// <param name="ListId">Maps to @ListId parameter.</param>
        /// <param name="Name">Maps to @Name parameter.</param>
        /// <param name="TypeFullName">Maps to @TypeFullName parameter.</param>
        /// <param name="Owner">Maps to @ModifiedBy parameter.</param>
        /// <param name="Data">Maps to @Data parameter.</param>
        /// <returns>
        /// An instance of <see cref="StoredProcedureResult"/> object
        /// </returns>
        internal StoredProcedureResult UpdateStorageItem(Guid WebApplicationId, Guid? SiteId, Guid? WebId, Guid? ListId,
            string Name, string TypeFullName, string ModifiedBy, byte[] Data)
        {
            SqlConnection connection = null;

            try
            {
                using (HostingEnvironment.Impersonate())
                {
                    connection = (SqlConnection)this.Connection;

                    var command = new SqlCommand(StoredProcedureNames.UpdateStorageItem, connection);
                    command.CommandType = CommandType.StoredProcedure;

                    // add the parameters
                    command.Parameters.Add(this.CreateParameter("Name", SqlDbType.NVarChar, 50, Name));
                    command.Parameters.Add(this.CreateParameter("WebApplicationId", SqlDbType.UniqueIdentifier, null,
                                                                WebApplicationId));
                    command.Parameters.Add(this.CreateNullableParameter("SiteId", SqlDbType.UniqueIdentifier, null,
                                                                SiteId));
                    command.Parameters.Add(this.CreateNullableParameter("WebId", SqlDbType.UniqueIdentifier, null,
                                                                WebId));
                    command.Parameters.Add(this.CreateNullableParameter("ListId", SqlDbType.UniqueIdentifier, null,
                                                                ListId));
                    command.Parameters.Add(this.CreateParameter("TypeFullName", SqlDbType.NVarChar, 256, TypeFullName));
                    command.Parameters.Add(this.CreateParameter("ModifiedBy", SqlDbType.NVarChar, 256, ModifiedBy));
                    command.Parameters.Add(this.CreateParameter("Data", SqlDbType.VarBinary, -1, Data));

                    // write to trace and execute
                    this.WriteSqlCommandTrace(command);

                    var reader = command.ExecuteReader();

                    return new StoredProcedureResult()
                    {
                        DataReader = reader,
                        ExecutionType = StoredProcedureExecutionType.ExecuteReader
                    };
                }
            }
            catch (Exception exception)
            {
                // ensure connection is closed
                if (connection != null)
                {
                    if (connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }
                }

                this.WriteErrorLog(exception, EventSeverity.Error);
                throw;
            }
        }

        /// <summary>
        /// Maps to sp_GetStorageItem stored procedure
        /// </summary>
        /// <param name="WebApplicationId">Maps to @WebApplicationId parameter.</param>
        /// <param name="SiteId">Maps to @SiteId parameter.</param>
        /// <param name="WebId">Maps to @WebId parameter.</param>
        /// <param name="ListId">Maps to @ListId parameter.</param>
        /// <param name="Name">Maps to @Name parameter.</param>
        /// <returns>
        /// An instance of <see cref="StoredProcedureResult"/> object
        /// </returns>
        internal StoredProcedureResult GetStorageItem(Guid WebApplicationId, Guid? SiteId, Guid? WebId, Guid? ListId,
            string Name)
        {
            SqlConnection connection = null;

            try
            {
                using (HostingEnvironment.Impersonate())
                {
                    connection = (SqlConnection)this.Connection;

                    var command = new SqlCommand(StoredProcedureNames.GetStorageItem, connection);
                    command.CommandType = CommandType.StoredProcedure;

                    // add the parameters
                    command.Parameters.Add(this.CreateParameter("Name", SqlDbType.NVarChar, 50, Name));
                    command.Parameters.Add(this.CreateParameter("WebApplicationId", SqlDbType.UniqueIdentifier, null,
                                                                WebApplicationId));
                    command.Parameters.Add(this.CreateNullableParameter("SiteId", SqlDbType.UniqueIdentifier, null,
                                                                SiteId));
                    command.Parameters.Add(this.CreateNullableParameter("WebId", SqlDbType.UniqueIdentifier, null,
                                                                WebId));
                    command.Parameters.Add(this.CreateNullableParameter("ListId", SqlDbType.UniqueIdentifier, null,
                                                                ListId));
                    // write to trace and execute
                    this.WriteSqlCommandTrace(command);

                    var reader = command.ExecuteReader();

                    return new StoredProcedureResult()
                    {
                        DataReader = reader,
                        ExecutionType = StoredProcedureExecutionType.ExecuteReader
                    };
                }
            }
            catch (Exception exception)
            {
                // ensure connection is closed
                if (connection != null)
                {
                    if (connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }
                }

                this.WriteErrorLog(exception, EventSeverity.Error);
                throw;
            }
        }

        /// <summary>
        /// Maps to sp_DeleteStorageItem stored procedure
        /// </summary>
        /// <param name="WebApplicationId">Maps to @WebApplicationId parameter.</param>
        /// <param name="SiteId">Maps to @SiteId parameter.</param>
        /// <param name="WebId">Maps to @WebId parameter.</param>
        /// <param name="ListId">Maps to @ListId parameter.</param>
        /// <param name="Name">Maps to @Name parameter.</param>
        /// <returns>
        /// An instance of <see cref="StoredProcedureResult"/> object
        /// </returns>
        internal StoredProcedureResult DeleteStorageItem(Guid WebApplicationId, Guid? SiteId, Guid? WebId, Guid? ListId,
            string Name)
        {
            SqlConnection connection = null;

            try
            {
                using (HostingEnvironment.Impersonate())
                {
                    connection = (SqlConnection)this.Connection;

                    var command = new SqlCommand(StoredProcedureNames.DeleteStorageItem, connection);
                    command.CommandType = CommandType.StoredProcedure;

                    // add the parameters
                    command.Parameters.Add(this.CreateParameter("Name", SqlDbType.NVarChar, 50, Name));
                    command.Parameters.Add(this.CreateParameter("WebApplicationId", SqlDbType.UniqueIdentifier, null,
                                                                WebApplicationId));
                    command.Parameters.Add(this.CreateNullableParameter("SiteId", SqlDbType.UniqueIdentifier, null,
                                                                SiteId));
                    command.Parameters.Add(this.CreateNullableParameter("WebId", SqlDbType.UniqueIdentifier, null,
                                                                WebId));
                    command.Parameters.Add(this.CreateNullableParameter("ListId", SqlDbType.UniqueIdentifier, null,
                                                                ListId));
                    // write to trace and execute
                    this.WriteSqlCommandTrace(command);

                    var recordsAffected = command.ExecuteNonQuery();

                    return new StoredProcedureResult()
                    {
                        RecordsAffected = recordsAffected,
                        ExecutionType = StoredProcedureExecutionType.ExecuteNonQuery
                    };
                }
            }
            catch (Exception exception)
            {
                // ensure connection is closed
                if (connection != null)
                {
                    if (connection.State != ConnectionState.Closed)
                    {
                        connection.Close();
                    }
                }

                this.WriteErrorLog(exception, EventSeverity.Error);
                throw;
            }
        }

        /// <summary>
        /// Gets an instance of the database connection to execute queries against
        /// </summary>
        private IDbConnection Connection
        {
            get
            {
                if (!String.IsNullOrEmpty(this.DatabaseConnectionString))
                {
                    var connection = new SqlConnection(this.DatabaseConnectionString);
                    connection.Open();

                    return connection;
                }

                return null;
            }
        }

        /// <summary>
        /// Writes an error event to the error logs
        /// </summary>
        /// <param name="ex">The exception details to log.</param>
        /// <param name="severity">The severity of the error.</param>
        private void WriteErrorLog(Exception ex, EventSeverity severity)
        {
            LoggingUtilities.WriteEventEntry(DiagnosticsAreas.CKSAS, DiagnosticCategories.ServiceDatabase,
                                             severity, ex);
        }

        /// <summary>
        /// Writes trace information related to the command that is going to be executed
        /// </summary>
        /// <param name="command">The command for which the information needs to be written.</param>
        private void WriteSqlCommandTrace(SqlCommand command)
        {
            var builder = new StringBuilder();
            builder.AppendFormat("Executing command {0} on {1}", command.CommandText,
                                 command.Connection.ConnectionString);
            builder.AppendLine();

            // process parameters
            foreach (SqlParameter parameter in command.Parameters)
            {
                if (parameter.Value == DBNull.Value)
                {
                    builder.AppendLine(String.Format("Parameter {0} Value: DBNULL", parameter.ParameterName));
                }
                else
                {
                    builder.AppendLine(String.Format("Parameter {0} Value: {1}", parameter.ParameterName,
                                                     parameter.Value.ToString()));
                }
            }

            LoggingUtilities.WriteTraceEntry(DiagnosticsAreas.CKSAS, DiagnosticCategories.ServiceDatabase, TraceSeverity.High,
                                             builder.ToString());
        }

        /// <summary>
        /// Creates a SQL parameter to be used in a command
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="name">The name of the parameter.</param>
        /// <param name="type">The type of the parameter.</param>
        /// <param name="size">The size of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <returns></returns>
        private SqlParameter CreateParameter<T>(string name, SqlDbType type, int? size, T value)
        {
            if (!name.StartsWith("@"))
            {
                name = "@" + name;
            }

            var newParam = new SqlParameter(name, value)
                               {
                                   SqlDbType = type
                               };

            if (size.HasValue)
            {
                newParam.Size = size.Value;
            }

            return newParam;
        }

        /// <summary>
        /// Creates a SQL parameter to be used in a command
        /// </summary>
        /// <param name="name">The name of the parameter.</param>
        /// <param name="type">The type of the parameter.</param>
        /// <param name="size">The size of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <returns></returns>
        private SqlParameter CreateNullableParameter(string name, SqlDbType type, int? size, object value)
        {
            if (!name.StartsWith("@"))
            {
                name = "@" + name;
            }

            var newParam = new SqlParameter(name, type);
            if (size.HasValue)
            {
                newParam.Size = size.Value;
            }

            newParam.Value = value ?? DBNull.Value;

            return newParam;
        }

        /// <summary>
        /// Creates a SQL parameter to be used in a command
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="name">The name of the parameter.</param>
        /// <param name="type">The type of the parameter.</param>
        /// <param name="size">The size of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <returns></returns>
        private SqlParameter CreateNullableParameter<T>(string name, SqlDbType type, int? size, T? value)
            where T : struct
        {
            if (!name.StartsWith("@"))
            {
                name = "@" + name;
            }

            var newParam = new SqlParameter(name, type);
            if (size.HasValue)
            {
                newParam.Size = size.Value;
            }

            if (value.HasValue)
            {
                newParam.Value = value.Value;
            }
            else
            {
                newParam.Value = DBNull.Value;
            }

            return newParam;
        }
    }
}